package cs4280.ass2.persistence;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.DbUtils;
import org.apache.log4j.Logger;

import cs4280.ass2.entity.Manager;
import cs4280.ass2.entity.User;

public class ManagerDao extends BaseDao {
	private static final Logger logger = Logger.getLogger(ManagerDao.class);
	private static final String _SELECT_BY_ID = "select * from APP_USER where ID = ? and TYPE = '" + User.USER_TYPE_MANAGER + "'";
	private static final String _SELECT_ALL = "select * from APP_USER where TYPE = '" + User.USER_TYPE_MANAGER + "' order by FIRST_NAME asc";
	private static final String _INSERT_NEW_MANAGER = "INSERT INTO [APP_USER] (CREATE_DATE, CREATE_USER, MODIFY_DATE, MODIFY_USER, FIRST_NAME, LAST_NAME, LOGIN, PASSWORD, TYPE,  APP_USER_STATUS) VALUES (?,?,?,?,?,?,?,?,?,?)";
	private static final String _UPDATE = "";
	private static final String _DELETE_MANAGER = "UPDATE APP_USER SET APP_USER_STATUS = '" + User.APP_USER_DELETED + "' WHERE ID = ?";

	private static ManagerDao managerDao = null;

	public static ManagerDao getInstance() {
		if (managerDao == null)
			managerDao = new ManagerDao();
		return managerDao;
	}

	public Manager retrieveManager(long id) {
		Manager manager = null;
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet result = null;

		try {
			connection = getConnection();
			statement = connection.prepareStatement(_SELECT_BY_ID,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			statement.setLong(1, id);
			result = statement.executeQuery();
			if (result.next()) {
				manager = new Manager();
				manager.setId(result.getLong("id"));
				manager.setFirstName(result.getString("first_name"));
				manager.setLastName(result.getString("last_name"));
				manager.setLogin(result.getString("login"));
				manager.setPassword(result.getString("password"));
				manager.setCreateDate(toUtilDate(result.getTimestamp("create_date")));
				manager.setCreateUser(result.getLong("create_user"));
				manager.setModifyDate(toUtilDate(result.getTimestamp("modify_date")));
				manager.setModifyUser(result.getLong("modify_user"));
			}
		} catch (SQLException e) {
			logger.error("Fail to retrieveManager ", e);
		} finally {
			DbUtils.closeQuietly(result);
			DbUtils.closeQuietly(statement);
			DbUtils.closeQuietly(connection);
		}
		return manager;
	}
	
	public List<Manager> retrieveAllManager() {
		List<Manager> managerList = new ArrayList<Manager>();
		Connection connection = null;
		Statement statement = null;
		ResultSet result = null;

		try {
			connection = getConnection();
			statement = connection.createStatement(
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			result = statement.executeQuery(_SELECT_ALL);

			Manager manager;
			while (result.next()) {
				manager = this.retrieveManager(result.getLong("id"));
				managerList.add(manager);
			}
		} catch (SQLException e) {
			logger.error("Fail to retrieveManager ", e);
		} finally {
			DbUtils.closeQuietly(result);
			DbUtils.closeQuietly(statement);
			DbUtils.closeQuietly(connection);
		}
		return managerList;
	}

	public void registerManager(Manager manager) {
		Connection connection = null;
		PreparedStatement statement = null;
		try {
			connection = getConnection();
			statement = connection.prepareStatement(_INSERT_NEW_MANAGER,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			
			statement.setTimestamp(1, toSQLDate(manager.getCreateDate()));
			statement.setLong(2, manager.getCreateUser());
			statement.setTimestamp(3, toSQLDate(manager.getModifyDate()));
			statement.setLong(4, manager.getModifyUser());
			statement.setString(5, manager.getFirstName());
			statement.setString(6, manager.getLastName());
			statement.setString(7,manager.getLogin());
			statement.setString(8, manager.getPassword());
			statement.setString(9, User.USER_TYPE_MANAGER);
			statement.setString(10,manager.getAppUserStatus());
			
			int rows = statement.executeUpdate();
			
		} catch (SQLException e) {
			logger.error("Fail to create manager ", e);
		} finally {
			DbUtils.closeQuietly(statement);
			DbUtils.closeQuietly(connection);
		}
	}

	public void updateManager(Manager manager) {
		
	}
	
	public void deleteManager(long id) {
		Connection connection = null;
		PreparedStatement pstat = null;
		try {
			connection = getConnection();
			pstat = connection.prepareStatement(_DELETE_MANAGER,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			
			pstat.setLong(1, id);
			int rows = pstat.executeUpdate();
			
		} catch (SQLException e) {
			logger.error("Fail to delete manager ", e);
		} finally {
			DbUtils.closeQuietly(pstat);
			DbUtils.closeQuietly(connection);
		}
	}
}
